# 转换Excel为清单
import pandas as pd
from src.utils import is_digits_and_uppercase
from src.utils import traversal_folder

# 报关资料所在文件夹（需要修改）
folder_path = "/Users/wangzhiwei/Desktop/报关资料/集装箱"
# 报关资料序号前缀路径（需要修改）
prePath = "集装箱/"
# 载货清单（需要修改）
output_path = "/Users/wangzhiwei/Desktop/载货清单-集装箱.xlsx"

def get_product_info(file_path):
    df = pd.read_excel(file_path, sheet_name='Sheet1')
    product_info = {
        "#No": None,
        "#Shipper": None,
        "#Consignee": None,
        "#Packages": None,
        "#GoodsName": None,
        "#GoodsNameCn": None,
        "#GWeight": None,
        "#NWeight": None,
        "#Number": None,
    }
    for col in range(len(df.columns)):
        for i in range(len(df)):
            value = df.iloc[i, col]
            if type(value) == str and "PACKING LIST" in value:
                product_info["#No"] = df.iloc[i + 3, col + 3]
                product_info["#Shipper"] = df.iloc[i - 3, col] + '\n' + df.iloc[i - 2, col]
                product_info["#Consignee"] = df.iloc[i + 5, col + 3] + "\n" + df.iloc[i + 6, col + 3]

                product_name_cn_list = None
                step = 11
                product_name_cn = df.iloc[i + step, col + 2]
                product_name_cn_map = {}
                while product_name_cn is not None and type(product_name_cn) is str and "TOTAL" not in product_name_cn:
                    if product_name_cn_map.get(product_name_cn) is None:
                        product_name_cn_map.setdefault(product_name_cn, 1)
                        if product_name_cn_list is None:
                            product_name_cn_list = product_name_cn
                        else:
                            product_name_cn_list += ("\n" + product_name_cn)
                    step+=1
                    product_name_cn = df.iloc[i + step, col + 2]
                product_info["#GoodsNameCn"] = product_name_cn_list

                product_name_list = None
                step = 11
                product_name = df.iloc[i + step, col + 6]
                product_name_map = {}
                while product_name is not None and type(product_name) is str and "TOTAL" not in product_name:
                    if product_name_map.get(product_name) is None:
                        product_name_map.setdefault(product_name, 1)
                        if product_name_list is None:
                            product_name_list = product_name
                        else:
                            product_name_list += ("\n" + product_name)
                    step+=1
                    product_name = df.iloc[i + step, col + 6]
                product_info["#GoodsName"] = product_name_list
            if type(value) == str and "TOTAL" in value:
                left_value = df.iloc[i, col - 2]
                if type(left_value) == str and "N/M" in left_value:
                    product_info["#Packages"] = f"{df.iloc[i, col + 11]}"
                    product_info["#GWeight"] = f"{df.iloc[i, col + 13]}"
                    product_info["#NWeight"] = f"{df.iloc[i, col + 14]}"

    return product_info
# 调用函数
files = traversal_folder(folder_path)
print("读取文件列表完成，开始打印...")
order_no_list = []
product_name_list = []
product_name_cn_list = []
pks_list = []
gross_list = []
net_list = []
size_list = []
shipper_list = []
consignee_list = []
for file in files:
    print("数据抽取中：" + file)
    extNameIndex = file.find('.xls')
    if extNameIndex >= 0:
        keywordIndex = file.find(prePath)
        number = ''
        i = keywordIndex + len(prePath)
        numberChar = file[i]
        while is_digits_and_uppercase(numberChar):
            number += numberChar
            i += 1
            numberChar = file[i]
        product_info = get_product_info(file)
        order_no_list.append(f"{number}")
        product_name_list.append(product_info["#GoodsName"])
        product_name_cn_list.append(product_info["#GoodsNameCn"])
        pks_list.append(product_info["#Packages"])
        gross_list.append(product_info["#GWeight"])
        shipper_list.append(product_info["#Shipper"])
        net_list.append(product_info["#NWeight"])
        consignee_list.append(product_info["#Consignee"])
    else:
        print("非表格文件，跳过")
print("数据导出中...")
result_data = {
    "Order No": order_no_list,
    "Product Name": product_name_list,
    "Product Name Cn": product_name_cn_list,
    "Pks": pks_list,
    "Gross": gross_list,
    "Net": net_list,
    "Shipper": shipper_list,
    "Consignee": consignee_list,
}
df = pd.DataFrame(result_data)
df.to_excel(output_path, index=False)

